﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Message]') AND type in (N'U'))
DROP TABLE [dbo].[Message]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Message](
	[Id] [uniqueidentifier] NOT NULL,
	[Recipient] [varchar](100) NOT NULL,
	[Data] [varbinary](max) NOT NULL,
	[DataFormat] [int] NOT NULL,
	[PutOn] [datetime] NOT NULL,
	CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO

SET ANSI_PADDING OFF
GO

CREATE NONCLUSTERED INDEX [IX_Recipient] ON [dbo].[Message] 
(
	[Recipient] ASC,
	[PutOn] ASC
)
GO

CREATE NONCLUSTERED INDEX [IX_PutOn] ON [dbo].[Message] 
(
	[PutOn] ASC
)
GO

ALTER TABLE [dbo].[Message] ADD  CONSTRAINT [DF_Message_PutOn]  DEFAULT (getdate()) FOR [PutOn]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Waiter]') AND type in (N'U'))
DROP TABLE [dbo].[Waiter]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Waiter](
	[Recipient] [varchar](100) NOT NULL,
	[WaiterId] [varchar](30) NOT NULL,
	[EnlistedOn] [datetime] NOT NULL,
	CONSTRAINT [PK_Waiters] PRIMARY KEY CLUSTERED 
	(
		[Recipient] ASC,
		[WaiterId] ASC
	)
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Waiter] ADD  CONSTRAINT [DF_Waiters_EnlistedOn]  DEFAULT (getdate()) FOR [EnlistedOn]
GO

CREATE NONCLUSTERED INDEX [IX_Recipient] ON [dbo].[Waiter] 
(
	[Recipient] ASC,
	[EnlistedOn] ASC
)
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TNVarcharSet' AND ss.name = N'dbo')
DROP TYPE [dbo].[TNVarcharSet]
GO

CREATE TYPE [dbo].[TNVarcharSet] AS TABLE
(
	[Value] nvarchar(256) NOT NULL,
	PRIMARY KEY CLUSTERED ([Value] ASC)
)
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TUniqueIdentifierSet' AND ss.name = N'dbo')
DROP TYPE [dbo].[TUniqueIdentifierSet]
GO

CREATE TYPE [dbo].[TUniqueIdentifierSet] AS TABLE
(
	[Value] uniqueidentifier NOT NULL,
	PRIMARY KEY CLUSTERED ([Value] ASC)
)
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TWaiterRow' AND ss.name = N'dbo')
DROP TYPE [dbo].[TWaiterRow]
GO

CREATE TYPE [dbo].[TWaiterRow] AS TABLE
(
	[Recipient] [varchar](100) NOT NULL,
	[WaiterId] [varchar](30) NOT NULL
	PRIMARY KEY CLUSTERED 
	(
		[Recipient] ASC,
		[WaiterId] ASC
	)
)
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TMessageRow' AND ss.name = N'dbo')
DROP TYPE [dbo].[TMessageRow]
GO

CREATE TYPE [dbo].[TMessageRow] AS TABLE
(
	[Id] [uniqueidentifier] NOT NULL,
	[Recipient] [varchar](100) NOT NULL,
	[Data] [varbinary](max) NOT NULL,
	[DataFormat] [int] NOT NULL,
	[PutOn] [datetime] NOT NULL,
	PRIMARY KEY CLUSTERED ([Id] ASC)
)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dequeue]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Dequeue]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Dequeue]
	@Recipient varchar(100),
	@WaiterId varchar(30)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @Data varbinary(max) = NULL
	DECLARE @DataFormat int = NULL
	DECLARE @MessageId uniqueidentifier = NULL

	SELECT TOP 1 @Data = Data, @DataFormat = DataFormat, @MessageId = Id
	FROM [Message]
	WHERE Recipient = @Recipient
	ORDER BY PutOn
	
	IF @MessageId IS NULL
	BEGIN
		MERGE Waiter T
		USING (SELECT @Recipient Recipient, @WaiterId WaiterId) S
		ON (T.Recipient = S.Recipient AND T.WaiterId = S.WaiterId)
		WHEN NOT MATCHED THEN
			INSERT(Recipient, WaiterId)
			VALUES(S.Recipient, S.WaiterId);
		
		SELECT TOP 0 NULL
	END
	ELSE
	BEGIN
		DELETE FROM [Message]
		WHERE Id = @MessageId
		
		SELECT @Data AS [Data], @DataFormat AS [DataFormat]
	END

	RETURN 0
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Enqueue]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Enqueue]
GO

CREATE PROCEDURE [dbo].[Enqueue]
	@Id uniqueidentifier,
	@Recipient varchar(100),
	@Data varbinary(max),
	@DataFormat int
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO [Message](Id, Recipient, Data, DataFormat)
	VALUES (@Id, @Recipient, @Data, @DataFormat)

	DECLARE @WaiterId varchar(30) = NULL
	
	SELECT TOP 1 @WaiterId = WaiterId
	FROM Waiter
	WHERE Recipient = @Recipient
	ORDER BY EnlistedOn ASC

	SELECT @WaiterId

	IF NOT @WaiterId IS NULL
		DELETE FROM Waiter
		WHERE Recipient = @Recipient AND WaiterId = @WaiterId

	RETURN 0
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetRecipients]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetRecipients]
GO

CREATE PROCEDURE [dbo].[GetRecipients]
	@Start bigint,
	@Limit bigint
AS
BEGIN
	SET NOCOUNT ON

	SELECT [Recipient]
	FROM (
		SELECT [Recipient], ROW_NUMBER() OVER (ORDER BY [Recipient] ASC) [Num]
		FROM (
			SELECT DISTINCT [Recipient]
			FROM [Message]
			UNION
			SELECT DISTINCT [Recipient]
			FROM [Waiter]
		) [DistinctRecipients]
	) [IndexedRecipients]
	WHERE [Num] BETWEEN @Start + 1 AND @Limit
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMessagesById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetMessagesById]
GO

CREATE PROCEDURE [dbo].[GetMessagesById]
	@Ids TUniqueIdentifierSet READONLY
AS
BEGIN
	SET NOCOUNT ON

	SELECT Id, Recipient, Data, DataFormat, PutOn
	FROM [Message]
	WHERE Id IN (SELECT Value FROM @Ids)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMessagesByRecipient]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetMessagesByRecipient]
GO

CREATE PROCEDURE [dbo].[GetMessagesByRecipient]
	@Recipients TNVarcharSet READONLY,
	@Start datetime,
	@End datetime
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @RcpCount int = (SELECT COUNT(Value) FROM @Recipients)

	SELECT Id, Recipient, Data, DataFormat, PutOn
	FROM [Message]
	WHERE 
		(@RcpCount = 0 OR Recipient IN (SELECT Value FROM @Recipients)) AND
		(@Start IS NULL OR PutOn >= @Start) AND
		(@End IS NULL OR PutOn <= @End)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetWaitersByRecipient]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetWaitersByRecipient]
GO

CREATE PROCEDURE [dbo].[GetWaitersByRecipient]
	@Recipients TNVarcharSet READONLY
AS
BEGIN
	SET NOCOUNT ON

	SELECT WaiterId, Recipient
	FROM Waiter
	WHERE Recipient IN (SELECT Value FROM @Recipients)
	ORDER BY EnlistedOn
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ImportRecipients]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ImportRecipients]
GO

CREATE PROCEDURE [dbo].[ImportRecipients]
	@Messages TMessageRow READONLY,
	@Waiters TWaiterRow READONLY
AS
BEGIN
	SET NOCOUNT ON

	MERGE [Message] T
	USING @Messages S
	ON (T.Id = S.Id)
	WHEN NOT MATCHED THEN
		INSERT(Id, Recipient, Data, DataFormat, PutOn)
		VALUES(S.Id, S.Recipient, S.Data, S.DataFormat, S.PutOn);

	MERGE Waiter T
	USING @Waiters S
	ON (T.Recipient = S.Recipient AND T.WaiterId = S.WaiterId)
	WHEN NOT MATCHED THEN
		INSERT(Recipient, WaiterId)
		VALUES(S.Recipient, S.WaiterId);
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RemoveMessages]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RemoveMessages]
GO

CREATE PROCEDURE [dbo].[RemoveMessages]
	@Ids TUniqueIdentifierSet READONLY
AS
BEGIN
	SET NOCOUNT ON

	DELETE FROM [Message]
	WHERE Id IN (SELECT Value FROM @Ids)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RemoveRecipients]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RemoveRecipients]
GO

CREATE PROCEDURE [dbo].[RemoveRecipients]
	@Recipients TNVarcharSet READONLY
AS
BEGIN
	SET NOCOUNT ON

	DELETE FROM [Message]
	WHERE Recipient IN (SELECT Value FROM @Recipients)

	DELETE FROM [Waiter]
	WHERE Recipient IN (SELECT Value FROM @Recipients)
END
GO